package com.css.fxfzypg.manage.lethalitydifference.repository.impl;

import com.css.fxfzypg.manage.lethalitydifference.repository.LethalityDifferenceRepository;
import com.css.fxfzypg.manage.lethalitydifference.repository.entity.LethalityDifferenceEntity;
import com.css.fxfzypg.manage.lethalitydifference.repository.entity.LethalityDifferenceFlagEntity;
import com.css.fxfzypg.manage.lethalitydifference.repository.entity.LethalityDifferenceItemEntity;
import com.css.fxfzypg.manage.lethalitydifference.vo.DistrictVo;
import com.css.fxfzypg.manage.lethalitydifference.vo.LethalityDifferenceParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class LethalityDifferenceRepositoryImpl implements LethalityDifferenceRepository {

    @Autowired
    JdbcTemplate jdbcTemplate;
    @Autowired
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Transactional
    @Override
    public boolean save(LethalityDifferenceEntity lethalityDifferenceEntity) {
        String sql = "insert into ypg_area_lethality_difference values(:id, :taskCode, :taskName, :province, :provinceId, :city, :cityId, :area, :areaId, " +
                ":building, :buildingWeightValue, :buildingExplain, :buildingFile, :peopleNum, :peopleNumWeightValue, :peopleNumExplain, :peopleNumFile, :terrain, :terrainWeightValue, :terrainExplain, :terrainFile, " +
                ":traffic, :trafficWeightValue, :trafficExplain, :trafficFile, :climate, :climateWeightValue, :climateExplain, :climateFile, " +
                " :finalValue, :isAdopt, :isSubmit, :delFlag," +
                " :createName, :createId, :createTime, :submitter, :submitterId, :submitTime, :maintainer, :maintainerId, :maintainerDepartment, :maintainTime," +
                " ST_GeometryFromText(:geom,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public Map<String, Object> querySubmitOrNot(String submitFlag, String taskCode, int curPage, int pageSize) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_area_lethality_difference where  del_flag = '0' and task_code ='" + taskCode + "' and is_submit ='" + submitFlag + "\'");
            if (submitFlag.equals("0")) {
                sql.append(" order by create_time desc");
            } else {
                sql.append(" order by submit_time desc");
            }
            int firstIndex = (curPage - 1) * pageSize;
            int lastIndex = pageSize;
            StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class)).size();
            List<LethalityDifferenceEntity> lethalityDifferenceEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
            Map<String, Object> map = new HashMap();
            map.put("total", total);
            map.put("rows", lethalityDifferenceEntityList);
            return map;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public boolean updateIsSubmit(Map<String, String> map) {
        String sql = " update ypg_area_lethality_difference set is_submit = 1, submitter = ?, submitter_id = ?, submit_time = ? where id = ?";
        return jdbcTemplate.update(sql, map.get("submitter"), map.get("submitterId"), new Date(), map.get("id")) > 0;
    }

    @Override
    public LethalityDifferenceEntity queryData(String id) {
        String sql = "select * from ypg_area_lethality_difference where  del_flag = '0' and id= ?";
        try {
            LethalityDifferenceEntity lethalityDifferenceEntity = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class), id);
            return lethalityDifferenceEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public Map<String, Object> queryDataList(HttpServletRequest request, LethalityDifferenceParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("SELECT t1.id,t1.province,t1.city,t1.area,t1.is_adopt,t1.submitter,t1.submit_time,t1.task_name,t1.task_code,t1.create_id,t2.task_status " +
                "from ypg_area_lethality_difference t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder groupSql = new StringBuilder(" group by t1.id,t1.province,t1.city,t1.area,t1.is_adopt,t1.submitter,t1.submit_time,t1.task_name,t1.task_code,t1.create_id,t2.task_status,t1.submit_time ");
        sql.append(whereSql).append(groupSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class)).size();
        List<LethalityDifferenceParams> lethalityDifferenceData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", lethalityDifferenceData);
        return map;
    }

    @Override
    public Map<String, Object> getDataByTaskCodeAndArea(String id, String isAdopt) {
        try {
            String sql = "select count(*) from ypg_area_lethality_difference where task_code=(select task_code from ypg_area_lethality_difference where id = '" + id + "') and area_id = (select area_id from ypg_area_lethality_difference where id = '" + id + "') and is_adopt = '" + isAdopt + "'";
            Map<String, Object> map = jdbcTemplate.queryForMap(sql);
            return map;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    @Override
    public boolean updateIsAdopt(String id, String isAdopt) {
        String sql = " update ypg_area_lethality_difference set is_adopt = ?  where id = ?";
        return jdbcTemplate.update(sql, isAdopt, id) > 0;
    }

    @Override
    public boolean modifyLethalityDifference(LethalityDifferenceEntity lethalityDifferenceEntity) {
        String sql = "update ypg_area_lethality_difference set " +
                "building=:building, building_weight_value=:buildingWeightValue, building_explain=:buildingExplain, building_file=:buildingFile, " +
                "people_num=:peopleNum,people_num_weight_value=:peopleNumWeightValue, people_num_explain=:peopleNumExplain, people_num_file=:peopleNumFile, " +
                "terrain=:terrain, terrain_weight_value=:terrainWeightValue, terrain_explain=:terrainExplain, terrain_file=:terrainFile, " +
                "traffic=:traffic, traffic_weight_value=:trafficWeightValue, traffic_explain=:trafficExplain, traffic_file=:trafficFile, " +
                "climate=:climate, climate_weight_value=:climateWeightValue, climate_explain=:climateExplain, climate_file=:climateFile, " +
                " is_submit=:isSubmit, submitter=:submitter, submitter_id=:submitterId, submit_time=:submitTime," +
                " maintainer=:maintainer, maintainer_id=:maintainerId, maintainer_department=:maintainerDepartment, maintain_time=:maintainTime where id = '" + lethalityDifferenceEntity.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public boolean deleteById(String id) {
        String sql = "update ypg_area_lethality_difference set del_flag = 1 where id =?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public Map<String, Object> queryLethalityDifferenceDataByProvince(LethalityDifferenceParams queryParams, int curPage, int pageSize, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.province,t1.city,t1.area,t1.is_adopt,t1.submitter,t1.submit_time,t1.task_name,t1.task_code,t1.create_id,t2.task_status from ypg_area_lethality_difference t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id  ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class)).size();
        List<LethalityDifferenceParams> lethalityDifferenceData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", lethalityDifferenceData);
        return map;
    }

    @Override
    public Map<String, Object> getLethalityDifferenceDataByTeamMembers(HttpServletRequest request, LethalityDifferenceParams queryParams, int curPage, int pageSize, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT DISTINCT t1.id,t1.province,t1.city,t1.area,t1.is_adopt,t1.submitter,t1.submit_time,t1.task_name,t1.task_code,t1.create_id,t2.task_status from ypg_area_lethality_difference t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class)).size();
        List<LethalityDifferenceParams> lethalityDifferenceData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", lethalityDifferenceData);
        return map;
    }

    @Override
    public Map<String, Object> queryPutDataList(HttpServletRequest request, LethalityDifferenceParams queryParams, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select id,province,city,area,maintainer,maintainer_department,maintain_time from ypg_area_lethality_difference");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and is_adopt='2'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and area_id = '" + queryParams.getAreaId() + "\'");
        }
        StringBuilder orderSql = new StringBuilder(" order by submit_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class)).size();
        List<LethalityDifferenceParams> lethalityDifferenceData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceParams.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", lethalityDifferenceData);
        return map;
    }

    @Override
    public boolean addWeightValue(LethalityDifferenceEntity lethalityDifferenceEntity) {
        String sql = "update ypg_area_lethality_difference set " +
                "building_weight_value=:buildingWeightValue, people_num_weight_value=:peopleNumWeightValue, terrain_weight_value=:terrainWeightValue, traffic_weight_value=:trafficWeightValue, climate_weight_value=:climateWeightValue," +
                " maintainer=:maintainer, maintainer_id=:maintainerId, maintainer_department=:maintainerDepartment, maintain_time=:maintainTime where id = '" + lethalityDifferenceEntity.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public List<LethalityDifferenceEntity> queryPutData(String taskId) {
        String sql = "select * from ypg_area_lethality_difference where del_flag = '0' and is_adopt='2' and task_code = (select task_num from ypg_survey_task where id='" + taskId + "')";
        List<LethalityDifferenceEntity> lethalityDifferenceData = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return lethalityDifferenceData;
    }

    @Override
    public boolean createFinalValue(LethalityDifferenceEntity lethalityDifferenceEntity) {
        String sql = "update ypg_area_lethality_difference set final_value=:finalValue where id = '" + lethalityDifferenceEntity.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public boolean saveItem(LethalityDifferenceItemEntity lethalityDifferenceItemEntity) {
        String sql = "insert into ypg_area_lethality_difference_item values(:id, :mainId, :customIndicatorName, :customIndicatorValue, :customIndicatorWeightValue, :customIndicatorExplain, :customIndicatorFile, :delFlag)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceItemEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public boolean modifyLethalityDifferenceItem(LethalityDifferenceItemEntity lethalityDifferenceItemEntity) {
        String sql = "update ypg_area_lethality_difference_item set " +
                "custom_indicator_name=:customIndicatorName, custom_indicator_value=:customIndicatorValue, custom_indicator_weight_value=:customIndicatorWeightValue, custom_indicator_explain=:customIndicatorExplain, custom_indicator_file=:customIndicatorFile where id = '" + lethalityDifferenceItemEntity.getId() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceItemEntity);
        return namedParameterJdbcTemplate.update(sql, source) > 0;
    }

    @Override
    public List<LethalityDifferenceItemEntity> queryItemData(String id) {
        String sql = "select * from ypg_area_lethality_difference_item where del_flag = '0' and main_id = '" + id + "'";
        List<LethalityDifferenceItemEntity> lethalityDifferenceItemEntities = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LethalityDifferenceItemEntity.class));
        return lethalityDifferenceItemEntities;
    }

    @Override
    public boolean deleteItemById(String id) {
        String sql = "update ypg_area_lethality_difference_item set del_flag = '1' where main_id =?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public boolean deleteCustomIndicatorById(String id) {
        String sql = "update ypg_area_lethality_difference_item set del_flag = '1' where id =?";
        return jdbcTemplate.update(sql, id) > 0;
    }

    @Override
    public boolean addItemWeightValue(String itemId, String weightValue) {
        String sql = "update ypg_area_lethality_difference_item set custom_indicator_weight_value = ? where id =?";
        return jdbcTemplate.update(sql, weightValue, itemId) > 0;
    }

    @Override
    public List<LethalityDifferenceEntity> queryLethalityDifference(LethalityDifferenceParams queryParams) {
        StringBuilder sql = new StringBuilder("select t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain " +
                "from ypg_area_lethality_difference t1 ");
        StringBuilder whereSql = new StringBuilder("where t1.del_flag = '0' and t1.is_submit = '1'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t1.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder groupSql = new StringBuilder(" group by t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain ");
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(groupSql).append(orderSql);
        List<LethalityDifferenceEntity> lethalityDifferenceData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return lethalityDifferenceData;
    }

    @Override
    public List<LethalityDifferenceEntity> queryLethalityDifferenceByProvince(LethalityDifferenceParams queryParams, String userId, String provinceId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain " +
                " from ypg_area_lethality_difference t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id  ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.province_id = '" + provinceId + "' or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder groupSql = new StringBuilder(" group by t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain ");
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(groupSql).append(orderSql);
        List<LethalityDifferenceEntity> lethalityDifferenceData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return lethalityDifferenceData;
    }

    @Override
    public List<LethalityDifferenceEntity> getLethalityDifferenceByTeamMembers(LethalityDifferenceParams queryParams, String userId, List<String> taskCodeList) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain " +
                " from ypg_area_lethality_difference t1 LEFT JOIN ypg_survey_task t2 on t1.task_code=t2.task_num " +
                "left join ypg_group t3 on t3.id = t2.group_id left join ypg_group_user t4 on t4.group_id = t3.id ");
        StringBuilder whereSql = new StringBuilder(" where t1.del_flag = '0' and t2.del_flag = '0' and t1.is_submit = '1' ");
        StringBuilder inSql = new StringBuilder();
        if (!PlatformObjectUtils.isEmpty(taskCodeList)) {
            for (int i = 0; i < taskCodeList.size(); i++) {
                inSql.append("'" + taskCodeList.get(i) + "'").append(",");
            }
            String in = inSql.toString();
            in = in.substring(0, in.length() - 1);
            whereSql.append(" and (t1.task_code in (" + in + ") or t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "') ");
        } else {
            whereSql.append(" and  (t1.create_id = '" + userId + "' or t3.group_leader_id = '" + userId + "' or t4.user_id ='" + userId + "')");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and t1.province_id = '" + queryParams.getProvinceId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and t1.city_id = '" + queryParams.getCityId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAreaId())) {
            whereSql.append(" and t1.area_id = '" + queryParams.getAreaId() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsAdopt())) {
            whereSql.append(" and t1.is_adopt = '" + queryParams.getIsAdopt() + "\'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getTaskName())) {
            whereSql.append(" and t2.task_name like '%" + queryParams.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getSubmitter())) {
            whereSql.append(" and t1.submitter like '%" + queryParams.getSubmitter() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStartTime()) && !PlatformObjectUtils.isEmpty(queryParams.getEndTime())) {
            String endTime = queryParams.getEndTime() + " 23:59:59";
            whereSql.append(" and t1.submit_time >= '" + queryParams.getStartTime() + "' and submit_time <= '" + endTime + "\'");
        }
        StringBuilder groupSql = new StringBuilder(" group by t1.id,t1.task_code,t1.province,t1.city,t1.area,t1.building,t1.building_explain,t1.people_num,t1.people_num_explain,t1.terrain,t1.terrain_explain,t1.traffic,t1.traffic_explain,t1.climate,t1.climate_explain ");
        StringBuilder orderSql = new StringBuilder(" order by t1.submit_time desc");
        sql.append(whereSql).append(groupSql).append(orderSql);
        List<LethalityDifferenceEntity> lethalityDifferenceData = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return lethalityDifferenceData;
    }

    @Override
    public LethalityDifferenceEntity getPutData(String provinceId, String cityId, String areaId) {
        try {
            StringBuilder sql = new StringBuilder("select id,province,province_id,city,city_id,area,area_id,building,building_weight_value,building_explain,people_num,people_num_weight_value,people_num_explain,terrain,terrain_weight_value,terrain_explain,traffic,traffic_weight_value,traffic_explain,climate,climate_weight_value,climate_explain,final_value,ST_AsText(geom) geom from ypg_area_lethality_difference");
            StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and is_adopt='2' and province_id = ? and city_id = ? and area_id = ?");
            LethalityDifferenceEntity lethalityDifferenceEntity = jdbcTemplate.queryForObject(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class), provinceId, cityId, areaId);
            return lethalityDifferenceEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    @Override
    public String getGeomByCode(String areaCode) {
        try {
            String sql = "select ST_AsText(geom) geom from district_boundary_county where code = ?";
            String geom = jdbcTemplate.queryForObject(sql, String.class, areaCode);
            return geom;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<DistrictVo> getAdoptedByTaskNum(String taskNum) {
        String sql = "select province,province_id,city,city_id,area,area_id from ypg_area_lethality_difference where del_flag = '0' and is_adopt = '1' and task_code='" + taskNum + "'";
        List<DistrictVo> adoptedData = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DistrictVo.class));
        return adoptedData;
    }

    @Override
    public List<LethalityDifferenceEntity> getAllPutData() {
        String sql = "select id,task_code,task_name,province,province_id,city,city_id,area,area_id,building,people_num,terrain,traffic,climate,is_adopt from ypg_area_lethality_difference where del_flag = '0' and is_adopt = '2'";
        List<LethalityDifferenceEntity> lethalityDifferenceEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return lethalityDifferenceEntityList;
    }

    @Override
    public List<LethalityDifferenceEntity> getAdoptedLethalityDifferenceByTaskNum(String taskNum) {
        String sql = "select id,task_code,task_name,province,province_id,city,city_id,area,area_id,building,people_num,terrain,traffic,climate,is_adopt from ypg_area_lethality_difference where del_flag = '0' and is_adopt = '1' and task_code='" + taskNum + "'";
        List<LethalityDifferenceEntity> adoptedData = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
        return adoptedData;
    }

    @Override
    public LethalityDifferenceEntity getPutDataByTaskNum(String taskNum, String provinceId, String cityId, String areaId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_area_lethality_difference");//and task_code = ?
            StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and is_adopt='2' and province_id = ? and city_id = ? and area_id = ?");
            LethalityDifferenceEntity lethalityDifferenceEntity = jdbcTemplate.queryForObject(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class), provinceId, cityId, areaId);
            return lethalityDifferenceEntity;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<LethalityDifferenceFlagEntity> getDataByTaskCode(String taskCode) {
        String sql = "select * from ypg_area_lethality_difference_flag where task_code = '" + taskCode + "'";
        List<LethalityDifferenceFlagEntity> lethalityDifferenceFlagEntityList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LethalityDifferenceFlagEntity.class));
        return lethalityDifferenceFlagEntityList;
    }

    @Override
    public void saveYpgAreaLethalityDifferenceFlag(LethalityDifferenceFlagEntity lethalityDifferenceFlagEntity) {
        String sql = "insert into ypg_area_lethality_difference_flag values(:id, :taskCode, :lethalityIds, :createFinalValueFlag)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(lethalityDifferenceFlagEntity);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from ypg_area_lethality_difference where province = '" + province + "' and del_flag = '0'", Integer.class);
    }

    @Override
    public Integer countByYearPrefixLike(String taskYear, String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) FROM ypg_area_lethality_difference where (TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%')  and province = '" + province + "' and del_flag = '0' ", Integer.class);

    }

    @Override
    public Map<String, Object> appQuerySubmitOrNot(String submitFlag, String taskCode, int curPage, int pageSize,String userId) {
        try {
            StringBuilder sql = new StringBuilder("select * from ypg_area_lethality_difference where  del_flag = '2' and create_id ='"+ userId +"' and task_code ='" + taskCode + "' and is_submit ='" + submitFlag + "\'");
            if (submitFlag.equals("0")) {
                sql.append(" order by create_time desc");
            } else {
                sql.append(" order by submit_time desc");
            }
            int firstIndex = (curPage - 1) * pageSize;
            int lastIndex = pageSize;
            StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
            Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class)).size();
            List<LethalityDifferenceEntity> lethalityDifferenceEntityList = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(LethalityDifferenceEntity.class));
            Map<String, Object> map = new HashMap();
            map.put("total", total);
            map.put("rows", lethalityDifferenceEntityList);
            return map;
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }


}
